package DataAccess;

import java.sql.*;
import java.util.*;
import java.awt.*;
import java.net.InetAddress;
import java.io.*;
import javax.swing.*;

import BackGround.*;
import Element.*;
import Element.Geom.*;
import DistSys.*;

public class DBAccessor {
  private final int PROGRESS_MIN=0;
  private final int PROGRESS_MAX=20;

  private boolean isOldStyleSystem=false;
  private int numOfElem=1;  //to update progress bar & setSize of Sys.elemList
  private Connection con;

  public static Frame mainFrame =null;

  public DBAccessor(){}

  private Connection getConnection( String mdbName) throws Exception{
    // for local DB with Data Source Name = PADTest1
      try{
          Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
          //String url="jdbc:odbc:PADTest1";  //DSN connection
          String url= "jdbc:odbc:DRIVER={Microsoft Access Driver (*.mdb)};"+
                      "DBQ="+mdbName;//c:\\JavaTest\\ePad1\\classes\\DBs\\testDB1.mdb";
          Connection c= DriverManager.getConnection(url);
          //JOptionPane.showMessageDialog(mainFrame, "CONNECTION: "+c.toString());
          return c;
      }catch(Exception e){
          JOptionPane.showMessageDialog(mainFrame, "EXCEPTION: "+e.toString());
          throw e;
      }
  }

/*  private Connection getConnection2() throws Exception{
    //for remote DB with Data Source Name = PADTest1
    try{
      // Register RmiJdbc Driver in jdbc DriverManager
      // On some platforms with some java VMs, newInstance() is necessary...
      Class.forName("RmiJdbc.RJDriver").newInstance();

      // Test with MS Access database (rjdemo ODBC data source)
      String url = "jdbc:odbc:PADTest1";
      //JOptionPane.showMessageDialog(mainFrame, "Step1-0k ");

      // RMI host will point to local host
      //String rmiHost = new String( "//"+SysConst.epadIP);//130.110.95.183");//127.0.0.1");//localhost");
       String rmiHost = new String("//" + InetAddress.getLocalHost().getHostName());
       JOptionPane.showMessageDialog(mainFrame, rmiHost);

      // RmiJdbc URL is of the form:
      // jdbc:rmi://<rmiHostName[:port]>/<jdbc-url>
      String s="jdbc:rmi:" + rmiHost + "/" + url;
      //JOptionPane.showMessageDialog(mainFrame, s);

      Connection c=DriverManager.getConnection("jdbc:rmi:" + rmiHost + "/" + url);
      //JOptionPane.showMessageDialog(mainFrame, "RJ Connection: \n" + c.toString());
      return c;
    }catch(Exception e){
        //System.out.println(e);
        JOptionPane.showMessageDialog( null,  "RJ Exception: \n"+e.toString());
        throw e;
    }
  }// end of getConnection
*/
  private void closeConnection () throws Exception {
      if( con.isClosed() == false ) con.close();
  }

  public boolean getIsOldStyleSystem(){ return this.isOldStyleSystem;}

  /**
   * This will initialize
   *    Gloabl base templates for a new project
   *    Active template for components
   *    Initial Shading options
   */
  public void initProject( String fileName) throws Exception{
      con = getConnection( fileName );

      Templates.reset();
      Vector vTmp = loadTemplatesTable();
      Templates.allList.addAll( vTmp);
      Templates.createSubList();

      loadDefaultComponentsTable();

      Sys.prjInfo.storedShadingOptions.removeAllElements();
      loadShadingOptionsTable();

      con.close();
  }

  //used directly if New Project is selected or the system is started
  //It's used as global template for Version 3
  //This is called when
  //    Open a version 2.0 *.mdb file;
  //    Load Templates from a version 3.0 *.mdb file
  public Vector loadTemplateFromMdbFile( String fileName )
  throws Exception{
      Vector  vTmp,
              vRtn; //a list of updated/added templates
      vRtn = new Vector(100, 100);
      con= this.getConnection(fileName);
      vTmp = loadTemplatesTable();  //vTmp contains all templates loaded;
                                    //Units synchronized with current unit in this call.

      AbsElem elem, elem2;
      for(int i=0; i<vTmp.size(); i++){
          elem = (AbsElem) vTmp.elementAt(i);
          elem2= Templates.insertIntoWithReplaceTempList( elem );
          vRtn.add( elem2 ); //must take elem2, NOT elem, due to replacement
      }
      Templates.createSubList();

      con.close();
      return vRtn;
  }//end of loadTemplateFromMdbFile( String fileName, boolean bLoadDftTmplt );

  public int importZoneFromProject( String fileName, double dImportedZone) throws Exception{
      Sys.clearSelectionAndFocus();
      con=this.getConnection(fileName);

      //Next block will add imported zones into current zone list
      //as long as the imported zone doesn't exist in current zone list
      Vector vTmpZone = (Vector) Sys.prjInfo.allZone.clone();
      Sys.prjInfo.allZone.removeAllElements();
      loadZoneDisplayTable();
      Vector vTmpZone2 = (Vector) Sys.prjInfo.allZone.clone();
      Sys.prjInfo.allZone.removeAllElements();
      Sys.prjInfo.allZone.addAll( vTmpZone );
      Zone znObj;
      for(int i=0; i< vTmpZone2.size(); i++){
          znObj = (Zone) vTmpZone2.get(i);
          if( znObj.getZoneNumber()==dImportedZone || dImportedZone==Zone.ENTIRE_SYSTEM ){
             Sys.prjInfo.addZoneIfUnexisted( znObj );
          }
      }

      AbsElem elem=null;
      Statement stmt = con.createStatement();
      String queryStr = "SELECT * FROM Components";// ORDER BY Zone ASC";
      ResultSet rs = stmt.executeQuery(queryStr);
      double dElemZone;

      //force isOldStyleSystem to be true, i.e. skip id fields in DB since
      //conn must be rebuilt and can't load conn thru id's.
      Vector vTmp =new Vector(1000,1000);
      int cntr=0;
      this.isOldStyleSystem=true;
      while(rs.next()){
          try {
              elem = loadElemFromRecord( rs );
              dElemZone = elem.getData(AbsElem.DATA_ZONE);
              if( dImportedZone==dElemZone || dImportedZone==Zone.ENTIRE_SYSTEM ){
//                  Sys.elemList.addElement(elem);  //add into elemList
//                  Sys.addElemToSelection( elem ); //add into selected list
//                  Sys.clearConn( elem);           //preventative redundency
//                  Sys.updateConn( elem );         //update conn.
//                  Sys.updateMaxAndMin(elem);      //update min and max (x,y)
                  Sys.addElem( elem, false, true );
                  vTmp.add( elem );
                  cntr++;
              }
          } catch(IllegalArgumentException e) {
              JOptionPane.showMessageDialog(mainFrame, "Error reading element '" +
                  (elem.getElemGeomObj() != null ? elem.getElemGeomObj().
                  getName() : "") + "': " + e.getMessage());
          }
      }//end of while for next record in RecordSet rs
      rs.close();
      stmt.close();

      stmt = con.createStatement();
      rs = stmt.executeQuery("SELECT * FROM [Project Information]");
      rs.next();
      int unitForTmp = SysConst.mapUnitFromStringToInt( rs.getString("Units") );
      stmt.close();
      //Sync unit of imported zone with current system
      if( Sys.prjInfo.unitOfLength != unitForTmp ){
          double k=Sys.findRatioBtwnUnits( Sys.prjInfo.unitOfLength, unitForTmp);
          Sys.multiplyLengthForVector( vTmp, k);
          Sys.divideDataInPerUnitForVector( vTmp, k);
      }

      con.close();
      return cntr;
  }//end of importZoneFromProject( )

  public boolean loadDataThruQuery ( String mdbName ) throws Exception{
    //boolean hasUnknownElem;
    con=getConnection( mdbName ); //local DB

    SysConst.progress = PROGRESS_MIN ; //min=0

    loadProjectTable();
    loadZoneDisplayTable();
    loadShadingOptionsTable();
    loadLayerTable();
    loadBackgroundTable();

    if( isOldStyleSystem ){
        con.close();  //!!!! close()
        //a new con will be set up next;
        //Load base/global templates; now it is a *.pad file
        //loadTemplateFromMdbFile( Sys.personalInfo.getGlobalDefaultTemplateFile());
        BinaryFileAccessor.loadTemplateFromPadFile(
            Sys.personalInfo.getGlobalDefaultTemplateFile() );
        //load prj-specific templates from *.dvd; replacement is enforced here.
        loadTemplateFromDvdFile( Sys.dvdFileName);

        con=getConnection(mdbName); //reconnect with original DB to load [Default Components]
        loadDefaultComponentsTable();
    }
    else{
        Vector v=loadTemplatesTable();
        Templates.insertIntoWithReplaceTempList( v );
        loadDefaultComponentsTable();
    }
    Templates.createSubList();

    // Templates are needed to be loaded before the first element, since the
    // element"s init method sets the real template objects based on the name.
    loadComponentsTable();  //return #of Unknown elem.; not used so far

    con.close();  //!!!! close()
    SysConst.progress = PROGRESS_MAX; //max=20;
    return false; //no unknown elem ; need to modify this
    //return hasUnknownElem;
  }// end of readDataThruQuery()

  public void saveDataThruQuery( String mdbName) throws Exception{
      con=getConnection( mdbName );

      SysConst.progress = PROGRESS_MIN ; //min=0
      saveProjectTable();
      saveComponentsTable();
      saveZoneDisplayTable();
      saveLayerTable();
      saveBackgroundTable();
      saveShadingTable();
      saveDefaultComponentsTable();
      saveTemplateTable();
      SysConst.progress = PROGRESS_MAX ; //max=20
      con.close();
  }

  //Tables [Templates] & [Project Info] should not be deleted
  //[Project Info] contains "Units" of templates.
  public void  cleanGlobalTemplate(String fileName) throws Exception{
      con= this.getConnection(fileName);
      Statement stmt=con.createStatement();
      stmt.executeUpdate("DELETE * FROM [Components]");
      stmt.executeUpdate("DELETE * FROM [Zone Display]");
      stmt.executeUpdate("DELETE * FROM [Background]");
      stmt.executeUpdate("DELETE * FROM [Default Components]");
      stmt.executeUpdate("DELETE * FROM [Layers]");
      stmt.executeUpdate("DELETE * FROM [Shading Options]");
      stmt.close();
      con.close();
  }

  private void saveProjectTable() throws Exception{
    Statement stmt = con.createStatement();
    String queryStr="SELECT Version FROM [Project Information]";
    ResultSet rs = stmt.executeQuery(queryStr);
    rs.next();
    double version= rs.getDouble(("Version"));
    if( version < 3.0 ){  isOldStyleSystem = true;}
    else isOldStyleSystem =false;

    //NOTE: Stored scale = lengthPerUnit *3.
    queryStr  = "UPDATE [Project Information] SET "+
                " Scale = "+ (Sys.prjInfo.lengthPerUnit * 3.0 ) +
                ",[# Components]="+Sys.elemList.size()+
                ", [Min X Coordinate] =" +Sys.SYS_MIN_X +
                ", [Min Y Coordinate] =" +Sys.SYS_MIN_Y +
                ", [Max X Coordinate] =" +Sys.SYS_MAX_X +
                ", [Max Y Coordinate] =" +Sys.SYS_MAX_Y +
                ", Units= '" + Sys.prjInfo.getUnitInString()+"'" +
                ", Version= 3.0" ;
    stmt.executeUpdate( queryStr);
    stmt.close();
  }

  private void saveComponentsTable ( ) throws SQLException{
    Statement stmt=con.createStatement();
    stmt.executeUpdate("DELETE * FROM Components");
    stmt.close();

    AbsElem e;
    AbsElemGeom g;
    ThreeNodeElemGeom g3;
    String queryStr="";
    int id;//String id;  //id must be integer so it makes sense when reading DB in ASC
    String conn1Str , conn2Str, conn3Str;

    int cntr=0, prevPrgrs=0, prgrs=0;
    Statement stmt2 = con.createStatement();
    Vector v = Sys.elemList;
    int sz = v.size();
    for(int i=0; i<sz; i++){
        e=(AbsElem)v.elementAt(i);
        g=e.getElemGeomObj();
        id= i;
        conn1Str = buildConnIntoString( g.conn1 );
        conn2Str = buildConnIntoString( g.conn2 );
        if( g instanceof ThreeNodeElemGeom ) {
            conn3Str = buildConnIntoString( ((ThreeNodeElemGeom) g).conn3 );
        } else {
            conn3Str = "";
        }

        queryStr ="INSERT INTO Components VALUES("
                  +generateSQLStringToSaveComponents( e )
                  +",'"+id+"','"
                  +conn1Str+"','"
                  +conn2Str+"','"
                  +conn3Str+"')";

        stmt2.addBatch( queryStr );

        cntr++;
        prgrs = (int) Math.round( (PROGRESS_MAX-1) * cntr/sz); //PROGRESS_MAX-1=19;
        if( prgrs >= (prevPrgrs+2)){//store in 9 section at 2,4,...16,18
            prevPrgrs = prgrs;
            SysConst.progress = prgrs;
            stmt2.executeBatch();
        }
    }//end of for loop

    stmt2.executeBatch(); //this is for (18-19) section
    stmt2.close();
  }

//  //Test with preparedStatement
//  private void saveComponentsTable_2 ( ) throws SQLException{
//    Statement stmt=con.createStatement();
//    stmt.executeUpdate("DELETE * FROM Components");
//    stmt.close();
//
//    AbsElem e;
//    AbsElemGeom g;
//    ThreeNodeElemGeom g3;
//    String queryStr="";
//    int id;//String id;  //id must be integer so it makes sense when reading DB in ASC
//    String conn1Str , conn2Str, conn3Str;
//
//    int cntr=0, prevPrgrs=0, prgrs=0;
//
//    StringBuffer str=new StringBuffer(512);
//    for(int i=0; i<129; i++){ str.append("?,");}
//    str.append("?");
//
//    queryStr ="INSERT INTO Components VALUES("+ str+")";
//    queryStr ="INSERT INTO Components (Name, Type, Description, "
//            +" [Default Template], X1, [Temporary Faults: /yr]) VALUES(?,?,?,?,?,?)";
//    System.out.println(queryStr);
//
//    PreparedStatement pstmt = con.prepareStatement( queryStr);
//    Vector v = Sys.elemList;
//    int sz = v.size();
//    for(int i=0; i<sz; i++){
//        e=(AbsElem)v.elementAt(i);
//        g=e.getElemGeomObj();
//        id= i;
//        conn1Str = buildConnIntoString( g.conn1 );
//        conn2Str = buildConnIntoString( g.conn2 );
//        if( g instanceof ThreeNodeElemGeom ) {
//            conn3Str = buildConnIntoString( ((ThreeNodeElemGeom) g).conn3 );
//        } else {
//            conn3Str = "";
//        }
//
//        pstmt.setString(1, g.getName());
//        pstmt.setString(2, this.mapTypeToString( e.getType() ) );
//        pstmt.setString(3, g.getDesc());
//        pstmt.setString(4, e.getDftTempName());
//        pstmt.setInt( 5, g.x1Stored);
//        pstmt.setInt( 6, g.y1Stored);
//        pstmt.setInt( 7, g.x1Stored);
//        pstmt.setInt( 8, g.y1Stored);
//        pstmt.setInt( 9, g.rotation);
//        pstmt.setInt( 10, g.LTC);
//        int isSwitched     = ((g instanceof ThreeNodeElemGeom &&
//                             ((ThreeNodeElemGeom)g).getIsSwitched()) ? 1 : 0);
//        int isOpen         = ((g instanceof SwtGeom &&
//                             ((SwtGeom)g).getIsOpen()) ? 1 : 0);
//        int[] showText = new int[12];
//        this.findShowText( e, showText);
//        pstmt.setInt( 11, isSwitched);
//        pstmt.setInt( 12, isOpen);
//        int OFFSET = 13;
//        for(int k=0; k<showText.length; k++){
//            pstmt.setInt( k + OFFSET, showText[k] );
//        }
//        OFFSET = 25;
//        int flag;
//        for(int k=0; k<AbsElem.NUM_DATA_FIELD; k++){
//            pstmt.setDouble(k*2 + OFFSET, 0);//e.getData(k));
//            flag = e.getFlag(k) & 0x00000003; //bit pattern: 0000...0011;
//            pstmt.setInt( k*2 + OFFSET + 1, 0); //flag );
//        }
//        pstmt.setInt(127, id ); //id = i;
//        pstmt.setString(128, conn1Str);
//        pstmt.setString(129, conn2Str);
//        pstmt.setString(130, conn3Str);
//        pstmt.addBatch();
//
//        cntr++;
//        prgrs = (int) Math.round( (PROGRESS_MAX-1) * cntr/sz); //PROGRESS_MAX-1=19;
//        if( prgrs >= (prevPrgrs+2)){//store in 9 section at 2,4,...16,18
//            prevPrgrs = prgrs;
//            SysConst.progress = prgrs;
//            pstmt.executeBatch();
//        }
//    }//end of for loop
//
//    pstmt.executeBatch();
//    pstmt.close();
//  }//end of saveComponentsTable_2();

  private void loadComponentsTable() throws SQLException{
    AbsElem elem=null;
    AbsElemGeom geom=null;
    int id=-1;
    int elemID=-1;

    Vector v = Sys.elemList;

    String queryStr ="SELECT * FROM Components";
    if(isOldStyleSystem == false ) queryStr +=" ORDER BY ID ASC";
    Statement stmt =con.createStatement();
    ResultSet rs=stmt.executeQuery(queryStr);

    int cntr=0;
    int prgrs =0, prevPrgrs=0;
    while(rs.next()){
        try {
            elem = loadElemFromRecord( rs );
            v.addElement(elem);
        } catch( SQLException e){
          //do nothing; Invalid descriptor index for version 1.0 MDB file
        } catch(IllegalArgumentException e) {
            JOptionPane.showMessageDialog(mainFrame, "Error reading element '" +
                (elem.getElemGeomObj() != null ? elem.getElemGeomObj().
                getName() : "") + "': " + e.getMessage());
        }
        cntr++;
        SysConst.progress = (int) Math.round(19.0*cntr/numOfElem);
    }//end of while for next record in RecordSet rs
    stmt.close();

    //find system connectivity:
    //for version 3.0, read conn. from DB
    //for version 2.0, call Sys.findConn()
    if( isOldStyleSystem ==true){
        //Sys.findSysConn();
        ;
    }
    else{
        //above approach may mess up the sequence of elements being read in
        //we need to keep the read-in sequence the same as in previous query
        //so that elemList.elementAt(id) keeps the integrity of the system.
        //In the future we can use rs.beforeFirst to rollback for the same
        //recordSet in the Enterprise version of JBuilder5.
        queryStr="SELECT * FROM Components ORDER BY ID ASC";//preventative redundency
        Statement stmt2 =con.createStatement();
        rs=stmt2.executeQuery(queryStr);

        //rs.beforeFirst();
        while(rs.next()){
            id= Integer.parseInt( rs.getString("ID") );//"ID"));
            elem=(AbsElem) v.elementAt(id);
            geom=elem.getElemGeomObj();
            extractConnFromString( geom.conn1, rs.getString("Conn 1"));//"Conn 1"
            extractConnFromString( geom.conn2, rs.getString("Conn 2"));//"Conn 2"
            if( geom instanceof ThreeNodeElemGeom ) {
                //some on-going testing prj at Lavelle's site does not have "conn 3"
                try{
                    extractConnFromString ( ((ThreeNodeElemGeom)geom).conn3,
                        rs.getString("Conn 3"));
                } catch(Exception e) { }
            }
        }//end of rs.next();
        stmt2.close();
    }// end of if // for version 3 only;
  }

  /**
   * create an elem from a row in Components table
   * 1. Load geom.data, ckt.data, rel.data
   * 2. create elem object
   * 3. load data flags by calling loadFlagFields()
   */
  private AbsElem loadElemFromRecord( ResultSet rs) throws SQLException{
      AbsElem elem=null;

      String name=rs.getString(1);  //"Name");
      String type= rs.getString(2); //"Type");
      String desc=rs.getString(3);  //"Description");
      String tmplt= rs.getString(4);//"Default Template");
      int x1Stored=rs.getInt(5); //"X1"));
      int y1Stored=rs.getInt(6); //"Y1"));
      int x2Stored=rs.getInt(7); //"X2"));
      int y2Stored=rs.getInt(8); //"Y2"));
        Point p1=new Point( x1Stored, y1Stored) ;  //for geom.initFromDB
        Point p2=new Point( x2Stored, y2Stored) ;  //for geom.initFromDB
        if( isOldStyleSystem && type.equalsIgnoreCase( "Transfer_Switch")){
            p2.x = p1.x + 4;    //In old PAD system, Transfer_Switch can't be
            p2.y = p1.y;        //toggled. And the size is fixed
        }
      int r =(rs.getInt(9) + 1) % 4;    //"Rotate" //DB.south=0; PAD.south=1;
      int LTC = rs.getInt(10);          //"LTC"

      boolean isSwitched, isOpen;
      int tmp=rs.getInt(11);//"Switched"));
          if(tmp ==1) isSwitched = true; else isSwitched = false;
      tmp=rs.getInt(12);//"Is Open"));
          if(tmp ==1) isOpen = true; else isOpen = false;

      //show text
      int[][] si = new int[AbsElemGeom.NUM_SHOW][];
      boolean sn = (rs.getInt(13) != 0); //"Show Name"));
      boolean sd = (rs.getInt(14) != 0); //"Show Desc"));

      int[] temp = new int[5];
      int temp_count = 0;
      for (int i = 0; i < 5; i++) {
          int old = rs.getInt(15+2*i); //"Show Data i"));
          if (old != 0) {
              // if data should be shown,
              // map it to our new index and append it to the list.
              temp[temp_count++] = mapOldDataIndexToElemDataIndex[old];
          }
      }
      if (temp_count > 0) {
          // There is data to be shown. Allocate an array of the correct size...
          si[AbsElemGeom.SHOW_DATA] = new int[temp_count];
          // ...copy the indices and...
          System.arraycopy(temp, 0, si[AbsElemGeom.SHOW_DATA], 0, temp_count);
          // ...sort them.
          Arrays.sort(si[AbsElemGeom.SHOW_DATA]);
      } else {
          // There is no data to be shown.
          si[AbsElemGeom.SHOW_DATA] = null;
      }

      temp_count = 0;
      for (int i = 0; i < 5; i++) {
          int old = rs.getInt(16+2*i); //"Show Results i"));
          if (old != 0)
              temp[temp_count++] = mapOldResultIndexToElemResultIndex[old];
      }
      if (temp_count > 0) {
          si[AbsElemGeom.SHOW_RESULT] = new int[temp_count];
          System.arraycopy(temp, 0, si[AbsElemGeom.SHOW_RESULT], 0, temp_count);
          Arrays.sort(si[AbsElemGeom.SHOW_RESULT]);
      } else
          si[AbsElemGeom.SHOW_RESULT] = null;

      int id=-1;
      if( !isOldStyleSystem ){ id=Integer.parseInt( rs.getString("ID")); } //"ID"

      elem = this.constructElemAndItsGeom( type, name, desc, r, LTC, sn, sd,
                                           si, p1, p2, isOpen, isSwitched );

      if (!elem.setDftTemp( tmplt )) {
          //JOptionPane.showMessageDialog(mainFrame,
          //    "Can't locate template for component '" + name + "'");
      }

      elem.setData( AbsElem.DATA_LENGTH,         rs.getDouble(25));//"Length"
      elem.setData( AbsElem.DATA_ZONE,           rs.getDouble(27 ));//"Zone"
      elem.setData( AbsElem.DATA_PHASE,          rs.getDouble(95 ));//"Phases (0-3)"
      elem.setData( AbsElem.DATA_FONT_SIZE,      rs.getDouble(97 ));//"Base Font Size"
      elem.setData( AbsElem.DATA_STUDY_AREA,     rs.getDouble(99 ));//"Study Area"
      elem.setData( AbsElem.DATA_LAYER,          rs.getDouble(109));//"Layer"

      //parameters related to load flow
      elem.setData( AbsElem.DATA_RESISTANCE,     rs.getDouble(67));//"Resistance: Ohms"
      elem.setData( AbsElem.DATA_REACTANCE,      rs.getDouble(69));//"Reactance: Ohms"
      elem.setData( AbsElem.DATA_SHUNT_COND,     rs.getDouble(71));//"Shunt Conductance: Mhos"
      elem.setData( AbsElem.DATA_SHUNT_SUCEP,    rs.getDouble(73));//"Shunt Suceptance: Mhos"
      elem.setData( AbsElem.DATA_CONST_POWER,    rs.getDouble(53));//"Constant Power: kVA"
      elem.setData( AbsElem.DATA_PF_CONST_POWER, rs.getDouble(55));//"PF of Constant Power"
      elem.setData( AbsElem.DATA_CONST_CURR,     rs.getDouble(57));//"Constant Current: Amps"
      elem.setData( AbsElem.DATA_PF_CONST_CURR,  rs.getDouble(59));//"PF of Constant Current"
      elem.setData( AbsElem.DATA_RATED_POWER,    rs.getDouble(61));//"Rated Power: kVA"
      elem.setData( AbsElem.DATA_RATED_CURR,     rs.getDouble(63));//"Rated Current: A"
      elem.setData( AbsElem.DATA_SRC_VOLT,       rs.getDouble(65));//"Source Voltage: kV"
      elem.setData( AbsElem.DATA_TXFM_RATIO,     rs.getDouble(75));//"Transformer Ratio: pu"
      elem.setData( AbsElem.DATA_TAP_RATIO,      rs.getDouble(77));//"Tap Ratio: pu"
      elem.setData( AbsElem.DATA_NUM_BOOST,      rs.getDouble(79));//"# Boost Taps"
      elem.setData( AbsElem.DATA_NUM_BUCK,       rs.getDouble(81));//"# Buck Taps"
      elem.setData( AbsElem.DATA_BOOST_TRIG,     rs.getDouble(83));//"Tap Boost Trigger: kV"
      elem.setData( AbsElem.DATA_BUCK_TRIG,      rs.getDouble(85));//"Tap Buck Trigger: kV"
      elem.setData( AbsElem.DATA_IS_LEADING,     rs.getDouble(91));//"Lagging = 0, Leading = 1"
      elem.setData( AbsElem.DATA_OPTIMIZABLE,    rs.getDouble(107)); //"Optimizable"
      elem.setData( AbsElem.DATA_IN_OPT_LIB,     rs.getDouble(105)); //"In Optimization Library"

      //parameters for Reliability Indices
      elem.setData( AbsElem.DATA_CUSTOMERS,    rs.getDouble(29));//"Customers"
      elem.setData( AbsElem.DATA_TEMP_FAULT,   rs.getDouble(31));//"Temporary Faults: /yr"
      elem.setData( AbsElem.DATA_SUST_FAULT,   rs.getDouble(33));//"Sustained Faults: /yr"
      elem.setData( AbsElem.DATA_OPEN_CKT,     rs.getDouble(35));//"Open Circuits: /yr"
      elem.setData( AbsElem.DATA_MTTR,         rs.getDouble(37));//"Mean Time To Repair: hr"
      elem.setData( AbsElem.DATA_MTTS,         rs.getDouble(39));//"Mean Time To Switch: hr"
      elem.setData( AbsElem.DATA_OPER_FAIL,    rs.getDouble(41));//"Operational Failures: pu"
      elem.setData( AbsElem.DATA_VOLT_SAG_LEVEL,     rs.getDouble(87));//"Critical Voltage Sag: pu"
      elem.setData( AbsElem.DATA_NORM_OPEN,    rs.getDouble(43));//"Normally Open"
      elem.setData( AbsElem.DATA_RECLOSING,    rs.getDouble(101));//"Reclosing Relay"
      elem.setData( AbsElem.DATA_INST_BLOCKED, rs.getDouble(103));//"Instantaneous Blocked"
      elem.setData( AbsElem.DATA_CAPITAL_COST, rs.getDouble(45));//"Capital Cost: $"
      elem.setData( AbsElem.DATA_OM_COST,      rs.getDouble(47));//"O & M Cost: $/yr"
      elem.setData( AbsElem.DATA_COST_PER_INT_KW,  rs.getDouble(49));//"Cost per Interruption: $/kW"
      elem.setData( AbsElem.DATA_COST_PER_INT_KWH, rs.getDouble(51));//"Cost per Interruption: $/kWh"
      elem.setData( AbsElem.DATA_COST_PER_SAG,     rs.getDouble(89));//"Cost per Critical Sag: $"
      elem.setData( AbsElem.DATA_MAINT_RATE,   rs.getDouble(111));//"Maintenance Rate: /yr"
      elem.setData( AbsElem.DATA_MAINT_DUR,    rs.getDouble(113));//"Maintenance Duration: hr"
      elem.setData( AbsElem.DATA_UPGRADE_COST, rs.getDouble(93));//"Upgrade Cost: $"

      elem.setData( AbsElem.DATA_ZONE_BUFFER0,   rs.getDouble(115));//"Zone Buffer 0"
      elem.setData( AbsElem.DATA_ZONE_BUFFER1,   rs.getDouble(117));//"Zone Buffer 1"
      elem.setData( AbsElem.DATA_ZONE_BUFFER2,   rs.getDouble(119));//"Zone Buffer 2"
      elem.setData( AbsElem.DATA_ZONE_BUFFER3,   rs.getDouble(121));//"Zone Buffer 3"
      elem.setData( AbsElem.DATA_ZONE_BUFFER4,   rs.getDouble(123));//"Zone Buffer 4"

      try{
        elem.setData( AbsElem.DATA_YEAR,           rs.getDouble(125));//"Year"
      } catch (SQLException e) {}

      loadFlagFields( elem, rs );

      return elem;
  }//end of loadElemFromRecord()

  static public String buildConnIntoString( Vector vConnList){
      StringBuffer strConn = new StringBuffer( 100 );
      int iConnElemId;
      for(int j=0; j<vConnList.size(); j++){
          iConnElemId = Sys.elemList.indexOf( vConnList.elementAt(j) );
          strConn.append( iConnElemId );
          strConn.append(';');
      }
      return strConn.toString();
  }//end of createConnString( );

  static public void extractConnFromString( Vector vConnList, String str ){
      int elemID;
      AbsElem elem;
      StringTokenizer strToken = new StringTokenizer( str, ";");
      while (strToken.hasMoreTokens()) {
          elemID = Integer.parseInt( strToken.nextToken() );
          if(elemID != -1 ) {
              elem = (AbsElem)Sys.elemList.elementAt(elemID);
              vConnList.add( elem );
          }
      }
  }//end of extractConnFromString();

  //construct and initialize elem & its associated geom
  static public AbsElem constructElemAndItsGeom(String type, String name,
  String desc, int r, int LTC, boolean sn, boolean sd, int[][] si,
  Point p1, Point p2, boolean isOpen, boolean isSwitched){
      AbsElem elem;
      AbsElemGeom geom;

      if( type.equals("Line") ){
          elem = new Line();
          geom = new LineGeom();
          ((LineGeom)geom).initFromDB(name, desc, r, LTC, sn, sd, si, p1, p2, false);
      }
      else if( type.equals("Cable") ){
          elem = new Line();
          geom = new LineGeom();
          ((LineGeom)geom).initFromDB(name, desc, r, LTC, sn, sd, si, p1, p2, true);
      }
      else if( type.equals("Horizontal_Bus") || type.equals("Vertical_Bus") ||type.equals("Bus") ){
          elem = new Bus();
          geom = new BusGeom();
          ((BusGeom)geom).initFromDB( name, desc, r, LTC, sn, sd, si, p1, p2);
      }
      else if( type.equals("Source") ){
          elem = new Source( );
          geom = new OneNodeElemGeom();
          ((OneNodeElemGeom)geom).initFromDB(name, desc, r, LTC, sn, sd, si, p1);
      }
      else if( type.equals("Distributed_Generation") ){
          elem = new DG();
          geom = new OneNodeElemGeom();
          ((OneNodeElemGeom)geom).initFromDB(name, desc, r, LTC, sn, sd, si, p1);
      }
      else if( type.equals("Transformer") ){
          elem = new Txfm();
          geom = new TwoNodeElemGeom();
          ((TwoNodeElemGeom)geom).initFromDB(name, desc, r, LTC, sn, sd, si, p1, p2);
      }
      else if( type.equals("Regulator") ){
          elem = new Regulator();
          geom = new TwoNodeElemGeom();
          ((TwoNodeElemGeom)geom).initFromDB(name, desc, r, LTC, sn, sd, si, p1, p2);
      }
      else if( type.equals("Breaker") ){
          elem = new Breaker();
          geom = new SwtGeom();
          ((SwtGeom)geom).initFromDB(name, desc, r, LTC, sn, sd, si, p1, p2, isOpen);
      }
      else if( type.equals("Drawout_Breaker") ){
          elem = new DrtBreaker();
          geom = new SwtGeom();
          ((SwtGeom)geom).initFromDB( name, desc, r, LTC, sn, sd, si, p1, p2, isOpen);
      }
      else if( type.equals("Recloser") ){
          elem = new Recloser();
          geom = new SwtGeom();
          ((SwtGeom)geom).initFromDB(name, desc, r, LTC, sn, sd, si, p1, p2, isOpen);
      }
      else if( type.equals("Switch") ){
          elem = new Switch();
          geom = new SwtGeom();
          ((SwtGeom)geom).initFromDB(name, desc, r, LTC, sn, sd, si, p1, p2, isOpen);
      }
      else if( type.equals("Transfer_Switch") ){
          elem = new Switch3();
          geom = new ThreeNodeElemGeom();
          ((ThreeNodeElemGeom)geom).initFromDB(name, desc, r, LTC, sn, sd, si, p1, p2, isSwitched);
      }
      else if( type.equals("Grounding_Switch") ){
          elem = new SwitchGnd();
          geom = new OneNodeElemGeom();
          ((OneNodeElemGeom)geom).initFromDB(name, desc, r, LTC, sn, sd, si, p1);
      }
      else if( type.equals("Fuse") ){
          elem = new Fuse();
          geom = new SwtGeom();
          ((SwtGeom)geom).initFromDB(name, desc, r, LTC, sn, sd, si, p1, p2, isOpen);
      }
      else if( type.equals("Sectionalizer") ){
          elem = new Sect();
          geom = new SwtGeom();
          ((SwtGeom)geom).initFromDB(name, desc, r, LTC, sn, sd, si, p1, p2, isOpen);
      }
      else if( type.equals("Load") ){
          elem = new Load();
          geom = new OneNodeElemGeom();
          ((OneNodeElemGeom)geom).initFromDB(name,desc, r, LTC, sn, sd, si, p1);
      }
      else if( type.equals("Series_Capacitor") ){
          elem = new SeriesCap();
          geom = new TwoNodeElemGeom();
          ((TwoNodeElemGeom)geom).initFromDB(name, desc, r, LTC, sn, sd, si, p1, p2);
      }
      else if( type.equals("Capacitor") ){
          elem = new Cap();
          geom = new OneNodeElemGeom();
          ((OneNodeElemGeom)geom).initFromDB(name, desc, r, LTC, sn, sd, si, p1);
      }
      else if( type.equals("Current_Transformer") ){
          elem = new CT();
          geom = new OneNodeElemGeom();
          ((OneNodeElemGeom)geom).initFromDB(name, desc, r, LTC, sn, sd, si, p1);
      }
      else if( type.equals("Motor") ){
          elem = new Motor();
          geom = new OneNodeElemGeom();
          ((OneNodeElemGeom)geom).initFromDB(name, desc, r, LTC, sn, sd, si, p1);
      }
      else if( type.equals("Reactor") ){
          elem = new Reactor();
          geom = new TwoNodeElemGeom();
          ((TwoNodeElemGeom)geom).initFromDB(name, desc, r, LTC, sn, sd, si, p1, p2);
      }
      else if( type.equals("Surge_Arrester") ){
          elem = new Arrester();
          geom = new OneNodeElemGeom();
          ((OneNodeElemGeom)geom).initFromDB(name, desc, r, LTC, sn, sd, si, p1);
      }
      else if( type.equals("Voltage_Transformer") ){
          elem = new PT();
          geom = new OneNodeElemGeom();
          ((OneNodeElemGeom)geom).initFromDB(name, desc, r, LTC, sn, sd, si, p1);
      }
      else if ( type.equals("Group") ){
          elem = new Group();
          geom = new GroupGeom();
          ((GroupGeom)geom).initFromDB(name, desc, r, LTC, sn, sd, si, p1, p2);
      }
      else{  // others
          elem = new DftElem();
          geom = new OneNodeElemGeom();
          ((OneNodeElemGeom)geom).initFromDB(name, desc, r, LTC, sn, sd, si, p1);
          //isElemTypeKnown = false;
      }
      elem.setElemGeomObj( geom);
      return elem;
  }

  /**
   * Load flags in a row (for one component) in Components table
   */
  private void loadFlagFields(AbsElem elem, ResultSet rs) throws SQLException{
      AbsElemGeom geom = elem.getElemGeomObj();

      elem.setFlag(AbsElem.DATA_LENGTH,       rs.getInt(26));
      elem.setFlag(AbsElem.DATA_ZONE,         rs.getInt(28));
      elem.setFlag(AbsElem.DATA_PHASE,        rs.getInt(96));
      elem.setFlag(AbsElem.DATA_FONT_SIZE,    rs.getInt(98));
      elem.setFlag(AbsElem.DATA_STUDY_AREA,   rs.getInt(100));
      elem.setFlag(AbsElem.DATA_LAYER,        rs.getInt(110));

      elem.setFlag(AbsElem.DATA_CUSTOMERS,        rs.getInt(30));
      elem.setFlag(AbsElem.DATA_TEMP_FAULT,       rs.getInt(32));
      elem.setFlag(AbsElem.DATA_SUST_FAULT,       rs.getInt(34));
      elem.setFlag(AbsElem.DATA_OPEN_CKT,         rs.getInt(36));
      elem.setFlag(AbsElem.DATA_MTTR,             rs.getInt(38));
      elem.setFlag(AbsElem.DATA_MTTS,             rs.getInt(40));
      elem.setFlag(AbsElem.DATA_OPER_FAIL,        rs.getInt(42));
      elem.setFlag(AbsElem.DATA_VOLT_SAG_LEVEL,   rs.getInt(88));
      elem.setFlag(AbsElem.DATA_NORM_OPEN,        rs.getInt(44));
      elem.setFlag(AbsElem.DATA_RECLOSING,        rs.getInt(102));
      elem.setFlag(AbsElem.DATA_INST_BLOCKED,     rs.getInt(104));
      elem.setFlag(AbsElem.DATA_CAPITAL_COST,     rs.getInt(46));
      elem.setFlag(AbsElem.DATA_OM_COST,          rs.getInt(48));
      elem.setFlag(AbsElem.DATA_COST_PER_INT_KW,  rs.getInt(50));
      elem.setFlag(AbsElem.DATA_COST_PER_INT_KWH, rs.getInt(52));
      elem.setFlag(AbsElem.DATA_COST_PER_SAG,     rs.getInt(90));
      elem.setFlag(AbsElem.DATA_MAINT_RATE,       rs.getInt(112));
      elem.setFlag(AbsElem.DATA_MAINT_DUR,        rs.getInt(114));
      elem.setFlag(AbsElem.DATA_UPGRADE_COST,     rs.getInt(94));

      elem.setFlag(AbsElem.DATA_RESISTANCE,     rs.getInt(68));
      elem.setFlag(AbsElem.DATA_REACTANCE,      rs.getInt(70));
      elem.setFlag(AbsElem.DATA_SHUNT_COND,     rs.getInt(72));
      elem.setFlag(AbsElem.DATA_SHUNT_SUCEP,    rs.getInt(74));
      elem.setFlag(AbsElem.DATA_CONST_POWER,    rs.getInt(54));
      elem.setFlag(AbsElem.DATA_PF_CONST_POWER, rs.getInt(56));
      elem.setFlag(AbsElem.DATA_CONST_CURR,     rs.getInt(58));
      elem.setFlag(AbsElem.DATA_PF_CONST_CURR,  rs.getInt(60));
      elem.setFlag(AbsElem.DATA_RATED_POWER,    rs.getInt(62));
      elem.setFlag(AbsElem.DATA_RATED_CURR,     rs.getInt(64));
      elem.setFlag(AbsElem.DATA_SRC_VOLT ,      rs.getInt(66));
      elem.setFlag(AbsElem.DATA_TXFM_RATIO,     rs.getInt(76));
      elem.setFlag(AbsElem.DATA_TAP_RATIO,      rs.getInt(78));
      elem.setFlag(AbsElem.DATA_NUM_BOOST,      rs.getInt(80));
      elem.setFlag(AbsElem.DATA_NUM_BUCK,       rs.getInt(82));
      elem.setFlag(AbsElem.DATA_BOOST_TRIG,     rs.getInt(84));
      elem.setFlag(AbsElem.DATA_BUCK_TRIG,      rs.getInt(86));
      elem.setFlag(AbsElem.DATA_IS_LEADING,     rs.getInt(92));
      elem.setFlag(AbsElem.DATA_OPTIMIZABLE,    rs.getInt(108));
      elem.setFlag(AbsElem.DATA_IN_OPT_LIB,     rs.getInt(106));

      elem.setFlag(AbsElem.DATA_ZONE_BUFFER0, rs.getInt(116));
      elem.setFlag(AbsElem.DATA_ZONE_BUFFER1, rs.getInt(118));
      elem.setFlag(AbsElem.DATA_ZONE_BUFFER2, rs.getInt(120));
      elem.setFlag(AbsElem.DATA_ZONE_BUFFER3, rs.getInt(122));
      elem.setFlag(AbsElem.DATA_ZONE_BUFFER4, rs.getInt(124));
      try{
          elem.setFlag(AbsElem.DATA_YEAR,         rs.getInt(126));
      }catch (SQLException e) { }
  }

  private void loadDefaultComponentsTable() throws Exception{
      Statement stmt = con.createStatement();
      String queryStr="SELECT * FROM [Default Components]";
      ResultSet rs = stmt.executeQuery(queryStr);
      String type, tempName;
      while(rs.next()){
          type=rs.getString("Component");
          tempName=rs.getString("Default");
          AbsElem activeTemp = Templates.mapStringToObject(tempName);

          //This will prevent an non-existed active template since initially
          //every component has a valid active template from global MDB file
          if(activeTemp == null) continue;

          if(type.equals("Line"))                 { Line.objActiveTemp = activeTemp;}
          else if(type.equals("Cable"))           { Line.objActiveTemp2 = activeTemp;}
          else if(type.equals("Load"))            { Load.objActiveTemp = activeTemp;}
          else if(type.equals("Transformer"))     { Txfm.objActiveTemp = activeTemp;}
          else if(type.equals("Regulator"))       { Regulator.objActiveTemp = activeTemp; }
          else if(type.equals("Breaker"))         { Breaker.objActiveTemp = activeTemp;}
          else if(type.equals("Drawout_Breaker")) { DrtBreaker.objActiveTemp = activeTemp;}
          else if(type.equals("Fuse"))            { Fuse.objActiveTemp = activeTemp;}
          else if(type.equals("Switch"))          { Switch.objActiveTemp = activeTemp;}
          else if(type.equals("Transfer_Switch")) { Switch3.objActiveTemp = activeTemp;}
          else if(type.equals("Grounding_Switch")){ SwitchGnd.objActiveTemp = activeTemp;}
          else if(type.equals("Recloser"))        { Recloser.objActiveTemp = activeTemp;}
          else if(type.equals("Sectionalizer"))   { Sect.objActiveTemp = activeTemp;}
          else if(type.equals("Capacitor"))       { Cap.objActiveTemp = activeTemp;}
          else if(type.equals("Reactor"))         { Reactor.objActiveTemp = activeTemp; }
          else if(type.equals("Series_Capacitor")){ SeriesCap.objActiveTemp = activeTemp; }
          else if(type.equals("Motor"))           { Motor.objActiveTemp = activeTemp;}
          else if(type.equals("Surge_Arrester"))  { Arrester.objActiveTemp = activeTemp;}
          else if(type.equals("Current_Transformer")){ CT.objActiveTemp = activeTemp;}
          else if(type.equals("Voltage_Transformer")){ PT.objActiveTemp = activeTemp;}
          else if(type.equals("Vertical_Bus"))    { Bus.objActiveTemp = activeTemp;}
          else if(type.equals("Horizontal_Bus"))  { Bus.objActiveTemp = activeTemp;}
          else if(type.equals("Bus"))             { Bus.objActiveTemp = activeTemp;}
          else if(type.equals("Distributed_Generation")){ DG.objActiveTemp = activeTemp;}
          else if(type.equals("Source"))          { Source.objActiveTemp = activeTemp;}
          else if(type.equals("Group"))           { Group.objActiveTemp = activeTemp;}
      }
  }

  private void loadProjectTable() throws Exception{
      Statement stmt=con.createStatement( ); //ResultSet.TYPE_SCROLL_SENSITIVE,
                              //ResultSet.CONCUR_READ_ONLY);

      String queryStr="SELECT * FROM [Project Information]";
      ResultSet rs2 = stmt.executeQuery(queryStr);
      rs2.next();
      numOfElem = rs2.getInt("# Components");
      //NOTE: Stored scale = 3.0 * lengthPerUnit
      Sys.prjInfo.lengthPerUnit = ( rs2.getDouble("Scale") )/3.0;
      //Sys.SYS_MAX_X = (int)(Double.parseDouble(rs2.getString("Max X Coordinate")));
      //Sys.SYS_MAX_Y = (int)(Double.parseDouble(rs2.getString("Max Y Coordinate")));
      //Sys.SYS_MIN_X = (int)(Double.parseDouble(rs2.getString("Min X Coordinate")));
      //Sys.SYS_MIN_Y = (int)(Double.parseDouble(rs2.getString("Min Y Coordinate")));

      double version= rs2.getDouble("Version");
      if( version < 3.0 ){  isOldStyleSystem = true;}
      else isOldStyleSystem =false;
      Sys.dvdFileName = rs2.getString("Default File");
      String unit = rs2.getString("Units");
      Sys.prjInfo.unitOfLength = SysConst.mapUnitFromStringToInt( unit);
      rs2.close();
      stmt.close();
  }


  //return a list of all templates in a *.mdb file
  private Vector loadTemplatesTable() throws Exception {
        Vector v = new Vector(100,100);
        AbsElem  elem;
        AbsElemGeom geom;
        int unitForTmp;
        double   dTmp;
        Statement stmt;
        ResultSet rs;
        try {
            stmt = con.createStatement();
            rs = stmt.executeQuery("SELECT * FROM [Project Information]");
            rs.next();
            unitForTmp = SysConst.mapUnitFromStringToInt( rs.getString("Units") );
            double version = rs.getDouble( "Version" );
            rs.close();
            stmt.close();
            if( version <3.0 ) return v; //v.size(0) =0;

            stmt = con.createStatement();
            rs = stmt.executeQuery("SELECT * FROM Templates");
        } catch(Exception e) {
            //return v;
            throw e; //Exception("Error Occured @ DBAccessor.loadTemplatesTable()\n");
        }

        while(rs.next()) {
            elem = DBAccessor.constructTmpltAndItsGeom( rs.getString("Type") );
            geom = elem.getElemGeomObj();
            try {
                //create geom object
                geom.setName(rs.getString("Name"));
                geom.setDesc(rs.getString("Description"));

                elem.setData(AbsElem.DATA_LENGTH,       rs.getDouble("Length"));
                elem.setData(AbsElem.DATA_ZONE,         rs.getDouble("Zone"));
                elem.setData(AbsElem.DATA_LAYER,        rs.getDouble("Layer"));
                elem.setData(AbsElem.DATA_PHASE,        rs.getDouble("Phases (0-3)"));
                elem.setData(AbsElem.DATA_FONT_SIZE,    rs.getDouble("Base Font Size"));
                elem.setData(AbsElem.DATA_STUDY_AREA,  (rs.getDouble("Study Area") > 0.01 ? 1 : 0));

                elem.setData(AbsElem.DATA_RESISTANCE,     rs.getDouble("Resistance: Ohms"));
                elem.setData(AbsElem.DATA_REACTANCE,      rs.getDouble("Reactance: Ohms"));
                elem.setData(AbsElem.DATA_SHUNT_COND,     rs.getDouble("Shunt Conductance: Mhos"));
                elem.setData(AbsElem.DATA_SHUNT_SUCEP,    rs.getDouble("Shunt Suceptance: Mhos"));
                elem.setData(AbsElem.DATA_CONST_POWER,    rs.getDouble("Constant Power: kVA"));
                elem.setData(AbsElem.DATA_PF_CONST_POWER, rs.getDouble("PF of Constant Power"));
                elem.setData(AbsElem.DATA_CONST_CURR,     rs.getDouble("Constant Current: Amps"));
                elem.setData(AbsElem.DATA_PF_CONST_CURR,  rs.getDouble("PF of Constant Current"));
                elem.setData(AbsElem.DATA_RATED_POWER,    rs.getDouble("Rated Power: kVA"));
                elem.setData(AbsElem.DATA_RATED_CURR,     rs.getDouble("Rated Current: A"));
                elem.setData(AbsElem.DATA_SRC_VOLT,       rs.getDouble("Source Voltage: kV"));
                elem.setData(AbsElem.DATA_TXFM_RATIO,     rs.getDouble("Transformer Ratio: pu"));
                elem.setData(AbsElem.DATA_TAP_RATIO,      rs.getDouble("Tap Ratio: pu"));
                elem.setData(AbsElem.DATA_NUM_BOOST,      rs.getDouble("# Boost Taps"));
                elem.setData(AbsElem.DATA_NUM_BUCK,       rs.getDouble("# Buck Taps"));
                elem.setData(AbsElem.DATA_BOOST_TRIG,     rs.getDouble("Tap Boost Trigger: kV"));
                elem.setData(AbsElem.DATA_BUCK_TRIG,      rs.getDouble("Tap Buck Trigger: kV"));
                elem.setData(AbsElem.DATA_IS_LEADING,    (rs.getDouble("Lagging = 0, Leading = 1") > 0.01 ? 1 : 0));
                elem.setData(AbsElem.DATA_OPTIMIZABLE,   (rs.getDouble("Optimizable") > 0.01 ? 1 : 0));
                elem.setData(AbsElem.DATA_IN_OPT_LIB,    (rs.getDouble("In Optimization Library") > 0.01 ? 1 : 0));

                elem.setData(AbsElem.DATA_CUSTOMERS,        rs.getDouble("Customers"));
                elem.setData(AbsElem.DATA_TEMP_FAULT,       rs.getDouble("Temporary Faults: /yr"));
                elem.setData(AbsElem.DATA_SUST_FAULT,       rs.getDouble("Sustained Faults: /yr"));
                elem.setData(AbsElem.DATA_OPEN_CKT,         rs.getDouble("Open Circuits: /yr"));
                elem.setData(AbsElem.DATA_MTTR,             rs.getDouble("Mean Time To Repair: hr"));
                elem.setData(AbsElem.DATA_MTTS,             rs.getDouble("Mean Time To Switch: hr"));
                elem.setData(AbsElem.DATA_OPER_FAIL,        rs.getDouble("Operational Failures: pu"));
                elem.setData(AbsElem.DATA_VOLT_SAG_LEVEL,   rs.getDouble("Critical Voltage Sag: pu"));
                elem.setData(AbsElem.DATA_NORM_OPEN,        rs.getDouble("Normally Open"));
                elem.setData(AbsElem.DATA_INST_BLOCKED,     rs.getDouble("Instantaneous Blocked"));
                elem.setData(AbsElem.DATA_RECLOSING,        rs.getDouble("Reclosing Relay"));
                elem.setData(AbsElem.DATA_CAPITAL_COST,     rs.getDouble("Capital Cost: $"));
                elem.setData(AbsElem.DATA_OM_COST,          rs.getDouble("O & M Cost: $/yr"));
                elem.setData(AbsElem.DATA_COST_PER_INT_KW,  rs.getDouble("Cost per Interruption: $/kW"));
                elem.setData(AbsElem.DATA_COST_PER_INT_KWH, rs.getDouble("Cost per Interruption: $/kWh"));
                elem.setData(AbsElem.DATA_COST_PER_SAG,     rs.getDouble("Cost per Critical Sag: $"));
                elem.setData(AbsElem.DATA_MAINT_RATE,       rs.getDouble("Maintenance Rate: /yr"));
                elem.setData(AbsElem.DATA_MAINT_DUR,        rs.getDouble("Maintenance Duration: hr"));
                elem.setData(AbsElem.DATA_UPGRADE_COST,     rs.getDouble("Upgrade Cost: $"));

                elem.setData(AbsElem.DATA_ZONE_BUFFER0, rs.getDouble("Zone Buffer 0"));
                elem.setData(AbsElem.DATA_ZONE_BUFFER1, rs.getDouble("Zone Buffer 1"));
                elem.setData(AbsElem.DATA_ZONE_BUFFER2, rs.getDouble("Zone Buffer 2"));
                elem.setData(AbsElem.DATA_ZONE_BUFFER3, rs.getDouble("Zone Buffer 3"));
                elem.setData(AbsElem.DATA_ZONE_BUFFER4, rs.getDouble("Zone Buffer 4"));
                elem.setData(AbsElem.DATA_YEAR,         rs.getDouble("Year"));

                elem.setElemGeomObj(geom);
                v.add( elem );
                //System.out.println("Step 1 : temp. size="+Templates.allList.size());
            } catch(IllegalArgumentException e) {
                JOptionPane.showMessageDialog(mainFrame, "Error reading template '" +
                    geom.getName() + "': " + e.getMessage());
            }
        }//end of while

        if( Sys.prjInfo.unitOfLength != unitForTmp ){
            double k=Sys.findRatioBtwnUnits( Sys.prjInfo.unitOfLength, unitForTmp);
            Templates.divideDataInPerUnitForVector(v, k);
        }

        stmt.close();
        return v;
    }//end of loadTemplatesTable()

    static public AbsElem constructTmpltAndItsGeom( String sType ){
            AbsElem elem;
            AbsElemGeom geom;
            // assign geom here so that it is initialized inside the catch block
            int iType = SysConst.mapStringToTemplType( sType);
            switch (iType) {
                case SysConst.TEMPLTYPE_LINE:
                case SysConst.TEMPLTYPE_CABLE:
                    elem = new Line();
                    geom = new LineGeom();
                    ((LineGeom)geom).setIsCable(iType == SysConst.TEMPLTYPE_CABLE);
                    break;
                case SysConst.TEMPLTYPE_LOAD:
                    elem = new Load();
                    geom = new OneNodeElemGeom();
                    break;
                case SysConst.TEMPLTYPE_PROTECTION:
                    elem = new Breaker();
                    geom = new SwtGeom();
                    break;
                case SysConst.TEMPLTYPE_SOURCE:
                    elem = new Source();
                    geom = new OneNodeElemGeom();
                    break;
                case SysConst.TEMPLTYPE_SWITCH:
                    elem = new Switch();
                    geom = new SwtGeom();
                    break;
                case SysConst.TEMPLTYPE_TRANSFORMER:
                    elem = new Txfm();
                    geom = new TwoNodeElemGeom();
                    break;
                case SysConst.TEMPLTYPE_MISC:
                    elem = new Bus();
                    geom = new BusGeom();
                    break;
                default:
                    elem = new DftElem();
                    geom = new OneNodeElemGeom();
            }//end of switch;
            elem.setElemGeomObj( geom);
            return elem;
    }//end of constructTmpltAndItsGeom

    // To map the index in DVD file or showText fields
    // into the associated index in AbsElem.adData[]
    private static final int[] mapOldDataIndexToElemDataIndex = new int[] {
        -1,                             //  0
        AbsElem.DATA_LENGTH,            //  1
        AbsElem.DATA_ZONE,              //  2
        AbsElem.DATA_CUSTOMERS,         //  3
        AbsElem.DATA_TEMP_FAULT,        //  4
        AbsElem.DATA_SUST_FAULT,        //  5
        AbsElem.DATA_OPEN_CKT,          //  6
        AbsElem.DATA_MTTR,              //  7
        AbsElem.DATA_MTTS,              //  8
        AbsElem.DATA_OPER_FAIL,         //  9
        AbsElem.DATA_NORM_OPEN,         // 10
        AbsElem.DATA_CAPITAL_COST,      // 11
        AbsElem.DATA_OM_COST,           // 12
        AbsElem.DATA_COST_PER_INT_KW,   // 13
        AbsElem.DATA_COST_PER_INT_KWH,  // 14
        AbsElem.DATA_CONST_POWER,       // 15
        AbsElem.DATA_PF_CONST_POWER,    // 16
        AbsElem.DATA_CONST_CURR,        // 17
        AbsElem.DATA_PF_CONST_CURR,     // 18
        AbsElem.DATA_RATED_POWER,       // 19
        AbsElem.DATA_RATED_CURR,        // 20
        AbsElem.DATA_SRC_VOLT,          // 21
        AbsElem.DATA_RESISTANCE,        // 22
        AbsElem.DATA_REACTANCE,         // 23
        AbsElem.DATA_SHUNT_COND,        // 24
        AbsElem.DATA_SHUNT_SUCEP,       // 25
        AbsElem.DATA_TXFM_RATIO,        // 26
        AbsElem.DATA_TAP_RATIO,         // 27
        AbsElem.DATA_NUM_BOOST,         // 28
        AbsElem.DATA_NUM_BUCK,          // 29
        AbsElem.DATA_BOOST_TRIG,        // 30
        AbsElem.DATA_BUCK_TRIG,         // 31
        AbsElem.DATA_VOLT_SAG_LEVEL,    // 32
        AbsElem.DATA_COST_PER_SAG,      // 33
        AbsElem.DATA_IS_LEADING,        // 34
        AbsElem.DATA_UPGRADE_COST,      // 35
        AbsElem.DATA_PHASE,             // 36
        AbsElem.DATA_FONT_SIZE,         // 37
        AbsElem.DATA_STUDY_AREA,        // 38
        AbsElem.DATA_RECLOSING,         // 39
        AbsElem.DATA_INST_BLOCKED,      // 40
        AbsElem.DATA_IN_OPT_LIB,        // 41
        AbsElem.DATA_OPTIMIZABLE,       // 42
        AbsElem.DATA_LAYER,             // 43
        AbsElem.DATA_MAINT_RATE,        // 44
        AbsElem.DATA_MAINT_DUR,         // 45
        AbsElem.DATA_ZONE_BUFFER0,      // 46
        AbsElem.DATA_ZONE_BUFFER1,      // 47
        AbsElem.DATA_ZONE_BUFFER2,      // 48
        AbsElem.DATA_ZONE_BUFFER3,      // 49
        AbsElem.DATA_ZONE_BUFFER4,      // 50
        AbsElem.DATA_YEAR,              // 51
    };

    // To map the index showText fields
    // into the associated index in AbsElem.adResult[]
    private static final int[] mapOldResultIndexToElemResultIndex = new int[] {
        -1,                            //  0
        AbsElem.RES_MOMENT_INT,        //  1
        AbsElem.RES_SUST_INT,          //  2
        AbsElem.RES_OUT_DUR,           //  3
        AbsElem.RES_SWT_OPER,          //  4
        AbsElem.RES_TRIP_OPER,         //  5
        AbsElem.RES_RECLOSE_OPER,      //  6
        AbsElem.RES_VOLT_SAGS,         //  7
        AbsElem.RES_MAIFI,             //  8
        AbsElem.RES_SAIFI,             //  9
        AbsElem.RES_SAIDI,             // 10
        AbsElem.RES_SAG,               // 11
        AbsElem.RES_VOLT_MAG,          // 12
        AbsElem.RES_VOLT_ANG,          // 13
        AbsElem.RES_CURR_MAG,          // 14
        AbsElem.RES_CURR_ANG,          // 15
        AbsElem.RES_PF,                // 16
        AbsElem.RES_VOLT_DROP,         // 17
        AbsElem.RES_TAP_POS,           // 18
        AbsElem.RES_CURR_LOADING,      // 19
        AbsElem.RES_KVA_FLOW,          // 20
        AbsElem.RES_VOLT_SAGS_ON_TGT,  // 21
        AbsElem.RES_CAPITAL_DELIV,     // 22
        AbsElem.RES_OM_DELIV,          // 23
        AbsElem.RES_LOAD_LOSS,         // 24
        AbsElem.RES_NO_LOAD_LOSS,      // 25
        AbsElem.RES_NET_DELIV_COST,    // 26
        AbsElem.RES_QUERY_SCORE_PERC,  // 27
        AbsElem.RES_SHORTCIRC_CURR,    // 28
        AbsElem.RES_BLOCK_OPER,        // 29
        AbsElem.RES_CAPA_CONS,         // 30
        AbsElem.RES_CAPA_CONS_KVA,     // 31
        AbsElem.RES_KVA_RATING,        // 32
        AbsElem.RES_VOLT_MAG_NODE1,    // 33
        AbsElem.RES_VOLT_ANG_NODE1,    // 34
    };

    public static String mapTypeToString(int type) {
        switch(type) {
            case SysConst.TYPE_ALL: return "All";
            case SysConst.TYPE_NONE: return "None";
            case SysConst.TYPE_LINE: return "Line";
            case SysConst.TYPE_CABLE: return "Cable";
            case SysConst.TYPE_LOAD: return "Load";
            case SysConst.TYPE_TRANSFORMER: return "Transformer";
            case SysConst.TYPE_REGULATOR: return "Regulator";
            case SysConst.TYPE_BREAKER: return "Breaker";
            case SysConst.TYPE_DRT_BREAKER: return "Drawout_Breaker";
            case SysConst.TYPE_FUSE: return "Fuse";
            case SysConst.TYPE_SWITCH: return "Switch";
            case SysConst.TYPE_SWITCH3: return "Transfer_Switch";
            case SysConst.TYPE_SWITCH_GND: return "Grounding_Switch";
            case SysConst.TYPE_RECLOSER: return "Recloser";
            case SysConst.TYPE_SECTIONIZER: return "Sectionalizer";
            case SysConst.TYPE_CAPACITOR: return "Capacitor";
            case SysConst.TYPE_REACTOR: return "Reactor";
            case SysConst.TYPE_SERIES_CAP: return "Series_Capacitor";
            case SysConst.TYPE_MOTOR: return "Motor";
            case SysConst.TYPE_ARRESTER: return "Surge_Arrester";
            case SysConst.TYPE_CURR_TX: return "Current_Transformer";
            case SysConst.TYPE_VOLT_TX: return "Voltage_Transformer";
            case SysConst.TYPE_BUS: return "Bus";
            case SysConst.TYPE_DIST_GEN: return "Distributed_Generation";
            case SysConst.TYPE_SOURCE: return "Source";
            case SysConst.TYPE_GROUP: return "Group";
            default: return null;
        }
    }

    private void loadTemplateFromDvdFile(String fileName){//for version 2 system only
        Vector list = new Vector(100,100); //linked list holding dftElem from *.dvd file
        int unitForTmp=0;
        try{
            AbsElem elem, e2;
            AbsElemGeom geom, g2;
            String s, name, desc, type;
            StringTokenizer st;
            int sz;
            File f=new File(fileName);
            BufferedReader br= new BufferedReader( new FileReader(fileName));
            for(int i=0;i<56;i++){ br.readLine();}
            s = br.readLine();  //Entry

            do{
                s  = br.readLine();  // name
                st = new StringTokenizer(s, "\"");
                st.nextToken();
                st.nextToken();
                String sName = st.nextToken();

                s  = br.readLine();  // type
                st = new StringTokenizer(s, "\"");
                st.nextToken();
                st.nextToken();
                switch (SysConst.mapStringToTemplType(st.nextToken())) {
                    case SysConst.TEMPLTYPE_LINE:
                    case SysConst.TEMPLTYPE_CABLE:
                        elem = new Line();
                        geom = new LineGeom();
                        break;
                    case SysConst.TEMPLTYPE_LOAD:
                        elem = new Load();
                        geom = new OneNodeElemGeom();
                        break;
                    case SysConst.TEMPLTYPE_PROTECTION:
                        elem = new Breaker();
                        geom = new SwtGeom();
                        break;
                    case SysConst.TEMPLTYPE_SOURCE:
                        elem = new Source();
                        geom = new OneNodeElemGeom();
                        break;
                    case SysConst.TEMPLTYPE_SWITCH:
                        elem = new Switch();
                        geom = new SwtGeom();
                        break;
                    case SysConst.TEMPLTYPE_TRANSFORMER:
                        elem = new Txfm();
                        geom = new TwoNodeElemGeom();
                        break;
                    case SysConst.TEMPLTYPE_MISC:
                        elem = new Bus();
                        geom = new BusGeom();
                        break;
                    default:
                        elem = new DftElem();
                        geom = new OneNodeElemGeom();
                }
                elem.setElemGeomObj(geom);
                geom.setName(sName);

                s  = br.readLine();  // desc
                st = new StringTokenizer(s, "\"");
                st.nextToken();
                st.nextToken();
                geom.setDesc(st.nextToken());

                br.readLine();

                for(int i=0;i<51;i++){
                    s = br.readLine();
                    st = new StringTokenizer(s, ",");
                    st.nextToken();

                    //elem.mapData( i, st.nextToken());  //reading data field
                    double val = Double.parseDouble(st.nextToken());
                    try {
                        elem.setData( mapOldDataIndexToElemDataIndex[i+1], val );
                        //elem.setValFromMappedDataIndex(mapElemData(i+1), val);
                    } catch(IllegalArgumentException e) {
                        JOptionPane.showMessageDialog(mainFrame,
                            "Error reading template '" + sName + "': " +
                            e.getMessage());
                    }
                }

                list.add(elem);
                //////Templates.insertIntoWithReplaceTempList(elem);

                br.readLine(); //dummy line between two entries

                s  = br.readLine();//Next Entry line or Unit line if the last line
                st = new StringTokenizer(s, "\",");

                s =st.nextToken();
                if( s.equals("Units: ")){
                    s = st.nextToken();
                    unitForTmp = SysConst.mapUnitFromStringToInt(s);
                    break;
                }
            }while( s.compareTo("Units:")!=0);
            br.close();

            //synchronize units for list with Sys
            if( Sys.prjInfo.unitOfLength != unitForTmp ){
                double k=Sys.findRatioBtwnUnits( Sys.prjInfo.unitOfLength, unitForTmp);
                Templates.divideDataInPerUnitForVector(list,k);
            }
            Templates.insertIntoWithReplaceTempList( list);
        }catch(Exception e){
            JOptionPane.showMessageDialog(mainFrame,
                "Failed to load Default Template:\n"+e);
        }
    }//end of loadTemplateFromDvdFile(String fileName)


  private void loadZoneDisplayTable() throws SQLException{
      String queryStr ="SELECT * FROM [Zone Display]";
      Statement stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery(queryStr);
      Color c;
      int rgb;
      boolean isExist;
      double theZone;
      while(rs.next() ){
          isExist = false;
          theZone = rs.getDouble("Zone");
          rgb = rs.getInt("Color");
          if( theZone==0.0) {c=Color.gray;} else {c = new Color( rgb );}
          Sys.prjInfo.addZoneIfUnexisted( theZone, c );
      }
      stmt.close();
  }//end of loadZoneDisplayTable();

  private void loadBackgroundTable() throws SQLException{
      BgLine bgLine;
      String sName;
      int iFontSize;
      int iThickness;
      int iStyle;
      Color c;
      int  iRGB;
      int x1Stored, y1Stored, x2Stored, y2Stored;

      String queryStr ="SELECT * FROM [Background]";
      Statement stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery(queryStr);
      while(rs.next() ){
          sName = rs.getString( "Name");
          iFontSize = rs.getInt("Font Size");
          iThickness = rs.getInt( "Width");
          iStyle = rs.getInt( "Style" );
          iRGB = rs.getInt("Color");
          c = new Color( iRGB );
          x1Stored = rs.getInt("X1");
          y1Stored = rs.getInt("Y1");
          x2Stored = rs.getInt("X2");
          y2Stored = rs.getInt("Y2");
          bgLine = new BgLine(sName, iFontSize, iThickness, iStyle, c,
                                  x1Stored, y1Stored, x2Stored, y2Stored );
          Sys.bgElemList.add( bgLine );
      }
      stmt.close();
  }//end of loadBackgroundTable();

    //used for old mdb format in PAD
    //shadingOpt is from old format mdb
    private static int mapOldShadingCatToResultIndex(int shadingCat) {
        switch(shadingCat) {
            case  0: return AbsElem.RES_MOMENT_INT;
            case  1: return AbsElem.RES_SUST_INT;
            case  2: return AbsElem.RES_OUT_DUR;
            case  3: return AbsElem.RES_SWT_OPER;
            case  4: return AbsElem.RES_TRIP_OPER;
            case  5: return AbsElem.RES_RECLOSE_OPER;
            case  6: return AbsElem.RES_VOLT_SAGS;
            case  7: return AbsElem.RES_MAIFI;
            case  8: return AbsElem.RES_SAIFI;
            case  9: return AbsElem.RES_SAIDI;
            case 10: return AbsElem.RES_SAG;
            case 11: return AbsElem.RES_VOLT_MAG;
            case 12: return AbsElem.RES_VOLT_ANG;
            case 13: return AbsElem.RES_CURR_MAG;
            case 14: return AbsElem.RES_CURR_ANG;
            case 15: return AbsElem.RES_PF;
            case 16: return AbsElem.RES_VOLT_DROP;
            case 17: return AbsElem.RES_TAP_POS;
            case 18: return AbsElem.RES_CURR_LOADING;
            case 19: return AbsElem.RES_KVA_FLOW;
            case 20: return AbsElem.RES_VOLT_SAGS_ON_TGT;
            case 21: return AbsElem.RES_CAPITAL_DELIV;
            case 22: return AbsElem.RES_OM_DELIV;
            case 23: return AbsElem.RES_LOAD_LOSS;
            case 24: return AbsElem.RES_NO_LOAD_LOSS;
            case 25: return AbsElem.RES_NET_DELIV_COST;
            case 26: return AbsElem.RES_QUERY_SCORE_PERC;
            case 27: return AbsElem.RES_SHORTCIRC_CURR;
            case 28: return AbsElem.RES_BLOCK_OPER;
            case 29: return AbsElem.RES_CAPA_CONS;
            case 30: return AbsElem.RES_CAPA_CONS_KVA;
            case 31: return AbsElem.RES_KVA_RATING;
            case 32: return AbsElem.RES_VOLT_MAG_NODE1;
            case 33: return AbsElem.RES_VOLT_ANG_NODE1;
            default: return AbsElem.RES_NONE;
        }
    }

  private void loadShadingOptionsTable() throws SQLException{
      Sys.prjInfo.storedShadingOptions.removeAllElements();
      String queryStr ="SELECT * FROM [Shading Options]";
      Statement stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery(queryStr);
      while(rs.next()){
          ShadingOption so = new ShadingOption();
          so.setDescription(rs.getString("Name"));
          so.setIsResult( true );
          //The "Data" column in DB really means Index of Results.
          if( isOldStyleSystem) {
              so.setIndex( mapOldShadingCatToResultIndex(rs.getInt("Data")));
          }else {
              so.setIndex(  rs.getInt("Data") );
          }
          so.setStartValue(rs.getDouble("Start"));
          so.setBinSize(rs.getDouble("Bin Size"));
          so.setAutoScale(false);
          so.setContinuous(false);
          Sys.prjInfo.storedShadingOptions.addElement(so);
      }
      stmt.close();
  }

  private void loadLayerTable() throws SQLException{
      String queryStr ="SELECT * FROM [Layers]";
      Statement stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery(queryStr);
      Layer layer;
      int id;
      Sys.prjInfo.initLayerList();
      while(rs.next() ){
          id = rs.getInt("Index");                  // [-2, -1, 0, 1, ..., 9]
          id += ProjectInfo.NUM_BACK_GND_LAYER; // [0, 1, 2,..., 11]
          if( id>=0 && id<ProjectInfo.NUM_LAYER ){
              layer = (Layer) Sys.prjInfo.layerList.get( id );
              layer.setName( rs.getString("Description"));
              layer.setMode( rs.getInt("Display"));
              layer.setEngine( rs.getInt("Engine"));
          }
      }
      stmt.close();
  }

  /*================================================
  Save information methods
  ================================================*/
  private void saveTemplateTable() {
    try{
      AbsElem elem;
      AbsElemGeom geom;
      String queryStr;
      double dStudyArea;
      Statement stmt = con.createStatement();

      int sz = Templates.allList.size();
      stmt.executeUpdate("DELETE * FROM Templates");
      for(int i=0;i<sz;i++){
          elem=(AbsElem)Templates.allList.elementAt(i);
          queryStr ="INSERT INTO Templates VALUES("
                  +generateSQLStringToSaveTemplates( elem )
                  +")";

          stmt.addBatch( queryStr );
      }
      stmt.executeBatch();
      stmt.close();
      con.close();
    }catch(Exception e){
        //System.out.println( e );
        JOptionPane.showMessageDialog(mainFrame,
            "Error Occured @ saveTemplateTable : "+e);
    }
  }

  private void saveZoneDisplayTable() throws SQLException{
      String queryStr;
      Statement stmt = con.createStatement();
      int sz = Sys.prjInfo.allZone.size();
      double number;
      int color;
      Zone z;
      stmt.executeUpdate("DELETE * FROM [Zone Display]");
      for(int i=0;i<sz;i++){
          z = (Zone) Sys.prjInfo.allZone.elementAt(i); //((Integer)Sys.prjInfo.zoneList.elementAt(i)).intValue();
          number = z.getZoneNumber();
          color = z.getZoneColor().getRGB();
          //color = ((Color)Sys.prjInfo.zoneColorList.elementAt(i)).getRGB();
          queryStr = "INSERT INTO [Zone Display] (Index,Zone,Color)"+
                      "VALUES("+i+","+number+","+color+")";
          stmt.addBatch( queryStr );
      }
      stmt.executeBatch();
      stmt.close();
  }

  private void saveBackgroundTable() throws SQLException{
      String queryStr;
      Statement stmt = con.createStatement();
      stmt.executeUpdate("DELETE * FROM Background");
      BgLine bgLine;
      LineGeom  bgLineGeom;

      int sz =  Sys.bgElemList.size();
      for(int i=0;i<sz;i++){
          bgLine = (BgLine) Sys.bgElemList.get(i);
          bgLineGeom = (LineGeom) bgLine.getElemGeomObj();
          queryStr = "INSERT INTO [Background]( Name, [Font Size], Width,"+
                     "Style, Color, X1, Y1, X2, Y2 ) "+
                      "VALUES("+
                      "'"+bgLine.getName() + "'," +
                      bgLine.getFontSize() + "," +
                      bgLine.getThickness() + "," +
                      bgLine.getStyle() + "," +
                      bgLine.getColor().getRGB() + "," +
                      bgLineGeom.x1Stored + "," +
                      bgLineGeom.y1Stored + "," +
                      bgLineGeom.x2Stored + "," +
                      bgLineGeom.y2Stored + ")";
          stmt.addBatch( queryStr );
      }
      stmt.executeBatch();
      stmt.close();
  }// end of saveBackgroundTable()

  private void saveLayerTable() throws SQLException{
      String queryStr;
      Statement stmt = con.createStatement();
      stmt.executeUpdate("DELETE * FROM Layers");
      int sz = Sys.prjInfo.layerList.size();
      Layer layer;
      for(int i=0;i<sz;i++){
          layer = (Layer) Sys.prjInfo.layerList.get(i);
          queryStr = "INSERT INTO [Layers] (Index,Description,Display,Engine)"+
                      "VALUES("+
                      layer.getIndex()+","+
                      "'"+layer.getName()+"',"+
                      layer.getMode()+","+
                      layer.getEngine()+ ")";
          stmt.addBatch( queryStr );
      }
      stmt.executeBatch();
      stmt.close();
  }

  private void saveShadingTable() throws SQLException{
      final int NUM_SHD_OPT=10;
      String queryStr;
      Statement stmt = con.createStatement();
      stmt.executeUpdate("DELETE * FROM [Shading Options]");
      int i;
      for(i = 0; i < Sys.prjInfo.storedShadingOptions.size(); i++) {
          ShadingOption so =
              (ShadingOption)Sys.prjInfo.storedShadingOptions.elementAt(i);
          queryStr =
              "INSERT INTO [Shading Options] " +
              "(Name, Data, Start, [Bin Size] )" +
              "VALUES(" +
              "'"+so.getDescription() +"',"+
              (so.getIsResult() ? so.getIndex() : 0 )+","+
              so.getStartValue()+","+
              so.getBinSize()+ ")";
          stmt.addBatch( queryStr );
      }
      for( ; i < NUM_SHD_OPT; i++) {
          queryStr =
              "INSERT INTO [Shading Options] " +
              "(Name, Data, Start, [Bin Size] )" +
              "VALUES(' ',0,0,1)";
          stmt.addBatch( queryStr );
      }
      stmt.executeBatch();
      stmt.close();
  }

  private void saveDefaultComponentsTable() throws SQLException{
      String queryStr;
      Statement stmt = con.createStatement();
      stmt.executeUpdate("DELETE * FROM [Default Components]");
      String header = "INSERT INTO [Default Components] " +
                      "(Index, Component, Default) VALUES(";
      String strTmplName;
      int cntr=1;
      queryStr= header +(cntr++)+ ",'Line','" +
                Line.objActiveTemp.getElemGeomObj().getName()+"')";
      stmt.execute( queryStr );
      queryStr= header +(cntr++)+ ",'Cable','" +
                Line.objActiveTemp2.getElemGeomObj().getName()+"')";
      stmt.addBatch( queryStr );
      queryStr= header +(cntr++)+ ",'Load','" +
                Load.objActiveTemp.getElemGeomObj().getName()+"')";
      stmt.addBatch( queryStr );
      queryStr= header +(cntr++)+ ",'Transformer','" +
                Txfm.objActiveTemp.getElemGeomObj().getName()+"')";
      stmt.addBatch( queryStr );
      queryStr= header +(cntr++)+ ",'Regulator','" +
                Regulator.objActiveTemp.getElemGeomObj().getName()+"')";
      stmt.addBatch( queryStr );
      queryStr= header +(cntr++)+ ",'Breaker','" +
                Breaker.objActiveTemp.getElemGeomObj().getName()+"')";
      stmt.addBatch( queryStr );
      queryStr= header +(cntr++)+ ",'Drawout_Breaker','" +
                DrtBreaker.objActiveTemp.getElemGeomObj().getName()+"')";
      stmt.addBatch( queryStr );
      queryStr= header +(cntr++)+ ",'Fuse','" +
                Fuse.objActiveTemp.getElemGeomObj().getName()+"')";
      stmt.addBatch( queryStr );
      queryStr= header +(cntr++)+ ",'Switch','" +
                Switch.objActiveTemp.getElemGeomObj().getName()+"')";
      stmt.addBatch( queryStr );
      queryStr= header +(cntr++)+ ",'Transfer_Switch','" +
                Switch3.objActiveTemp.getElemGeomObj().getName()+"')";
      stmt.addBatch( queryStr );
      queryStr= header +(cntr++)+ ",'Grounding_Switch','" +
                SwitchGnd.objActiveTemp.getElemGeomObj().getName()+"')";
      stmt.addBatch( queryStr );
      queryStr= header +(cntr++)+ ",'Recloser','" +
                Recloser.objActiveTemp.getElemGeomObj().getName()+"')";
      stmt.addBatch( queryStr );
      queryStr= header +(cntr++)+ ",'Sectionalizer','" +
                Sect.objActiveTemp.getElemGeomObj().getName()+"')";
      stmt.addBatch( queryStr );
      queryStr= header +(cntr++)+ ",'Capacitor','" +
                Cap.objActiveTemp.getElemGeomObj().getName()+"')";
      stmt.addBatch( queryStr );
      queryStr= header +(cntr++)+ ",'Reactor','" +
                Reactor.objActiveTemp.getElemGeomObj().getName()+"')";
      stmt.addBatch( queryStr );
      queryStr= header +(cntr++)+ ",'Series_Capacitor','" +
                SeriesCap.objActiveTemp.getElemGeomObj().getName()+"')";
      stmt.addBatch( queryStr );
      queryStr= header +(cntr++)+ ",'Motor','" +
                Motor.objActiveTemp.getElemGeomObj().getName()+"')";
      stmt.addBatch( queryStr );
      queryStr= header +(cntr++)+ ",'Surge_Arrester','" +
                Arrester.objActiveTemp.getElemGeomObj().getName()+"')";
      stmt.addBatch( queryStr );
      queryStr= header +(cntr++)+ ",'Current_Transformer','" +
                CT.objActiveTemp.getElemGeomObj().getName()+"')";
      stmt.addBatch( queryStr );
      queryStr= header +(cntr++)+ ",'Voltage_Transformer','" +
                PT.objActiveTemp.getElemGeomObj().getName()+"')";
      stmt.addBatch( queryStr );
      queryStr= header +(cntr++)+ ",'Bus','" +
                Bus.objActiveTemp.getElemGeomObj().getName()+"')";
      stmt.addBatch( queryStr );
      queryStr= header +(cntr++)+ ",'Distributed_Generation','" +
                DG.objActiveTemp.getElemGeomObj().getName()+"')";
      stmt.addBatch( queryStr );
      queryStr= header +(cntr++)+ ",'Source','" +
                Source.objActiveTemp.getElemGeomObj().getName()+"')";
      stmt.addBatch( queryStr );
      queryStr= header +(cntr++)+ ",'Group','" +
                Group.objActiveTemp.getElemGeomObj().getName()+"')";
      stmt.addBatch( queryStr );

      stmt.executeBatch();
      stmt.close();
  }//end of saveDefaultComponentsTable

  private String generateSQLStringToSaveTemplates(AbsElem elem ){
      AbsElemGeom geom = elem.getElemGeomObj();
      int isNormOpen     = (elem.getIsNormOpen() ? 1 : 0);
      int isLeading      = (elem.getIsLeading() ? 1 : 0);
      int isInStudyArea  = (elem.isInStudyArea() ? 1 : 0);
      int reclosingRelay = (elem.getHasReclosingRelay() ? 1 : 0);
      int instantBlocked = (elem.getIsInstantBlocked() ? 1 : 0);
      int isInOptLib     = (elem.getIsInOptLib() ? 1 : 0);
      int isOptimizable  = (elem.getIsOptimizable() ? 1 : 0);

      String str= "'"
          + geom.getName()+"','"
          + SysConst.ssTemplType[SysConst.mapTemplTypeToIndex(elem.getType())]+"','"
          + geom.getDesc()+"',"
          + elem.getData(AbsElem.DATA_LENGTH) +","
          + elem.getData(AbsElem.DATA_ZONE) +","
          + elem.getData(AbsElem.DATA_CUSTOMERS) +","
          + elem.getData(AbsElem.DATA_TEMP_FAULT) +","
          + elem.getData(AbsElem.DATA_SUST_FAULT) +","
          + elem.getData(AbsElem.DATA_OPEN_CKT) +","
          + elem.getData(AbsElem.DATA_MTTR) +","
          + elem.getData(AbsElem.DATA_MTTS) +","
          + elem.getData(AbsElem.DATA_OPER_FAIL)  +","
          + isNormOpen +","
          + elem.getData(AbsElem.DATA_CAPITAL_COST) +","
          + elem.getData(AbsElem.DATA_OM_COST) +","
          + elem.getData(AbsElem.DATA_COST_PER_INT_KW) +","
          + elem.getData(AbsElem.DATA_COST_PER_INT_KWH) +","
          + elem.getData(AbsElem.DATA_CONST_POWER) +","
          + elem.getData(AbsElem.DATA_PF_CONST_POWER) +","
          + elem.getData(AbsElem.DATA_CONST_CURR) +","
          + elem.getData(AbsElem.DATA_PF_CONST_CURR) +","
          + elem.getData(AbsElem.DATA_RATED_POWER) +","
          + elem.getData(AbsElem.DATA_RATED_CURR) +","
          + elem.getData(AbsElem.DATA_SRC_VOLT) +","
          + elem.getData(AbsElem.DATA_RESISTANCE) +","
          + elem.getData(AbsElem.DATA_REACTANCE) +","
          + elem.getData(AbsElem.DATA_SHUNT_COND) +","
          + elem.getData(AbsElem.DATA_SHUNT_SUCEP) +","
          + elem.getData(AbsElem.DATA_TXFM_RATIO) +","
          + elem.getData(AbsElem.DATA_TAP_RATIO) +","
          + elem.getData(AbsElem.DATA_NUM_BOOST) +","
          + elem.getData(AbsElem.DATA_NUM_BUCK) +","
          + elem.getData(AbsElem.DATA_BOOST_TRIG) +","
          + elem.getData(AbsElem.DATA_BUCK_TRIG) +","
          + elem.getData(AbsElem.DATA_VOLT_SAG_LEVEL) +","
          + elem.getData(AbsElem.DATA_COST_PER_SAG) +","
          + isLeading +","
          + elem.getData(AbsElem.DATA_UPGRADE_COST) +","
          + elem.getData(AbsElem.DATA_PHASE) +","
          + elem.getData(AbsElem.DATA_FONT_SIZE) +","
          + isInStudyArea +","
          + reclosingRelay +","
          + instantBlocked +","
          + isInOptLib +","
          + isOptimizable +","
          + elem.getData(AbsElem.DATA_LAYER) +","
          + elem.getData(AbsElem.DATA_MAINT_RATE) +","
          + elem.getData(AbsElem.DATA_MAINT_DUR) +","
          + elem.getData(AbsElem.DATA_ZONE_BUFFER0) +","
          + elem.getData(AbsElem.DATA_ZONE_BUFFER1) +","
          + elem.getData(AbsElem.DATA_ZONE_BUFFER2) +","
          + elem.getData(AbsElem.DATA_ZONE_BUFFER3) +","
          + elem.getData(AbsElem.DATA_ZONE_BUFFER4) +","
          + elem.getData(AbsElem.DATA_LAYER) ;
      return str;
  }//end of generateSQLStringToSaveTemplates();

  private String generateSQLStringToSaveComponents(AbsElem elem){
      AbsElemGeom geom = elem.getElemGeomObj();
      int isSwitched     = ((geom instanceof ThreeNodeElemGeom &&
                             ((ThreeNodeElemGeom)geom).getIsSwitched()) ? 1 : 0);
      int isOpen         = ((geom instanceof SwtGeom &&
                             ((SwtGeom)geom).getIsOpen()) ? 1 : 0);
      int isNormOpen     = (elem.getIsNormOpen() ? 1 : 0);
      int isLeading      = (elem.getIsLeading() ? 1 : 0);
      int isInStudyArea  = (elem.isInStudyArea() ? 1 : 0);
      int reclosingRelay = (elem.getHasReclosingRelay() ? 1 : 0);
      int instantBlocked = (elem.getIsInstantBlocked() ? 1 : 0);
      int isInOptLib     = (elem.getIsInOptLib() ? 1 : 0);
      int isOptimizable  = (elem.getIsOptimizable() ? 1 : 0);

      int[] showText = new int[12];
      findShowText(elem, showText);

      StringBuffer str=new StringBuffer(2048);
          str.append("'");
          str.append(geom.getName() +"' , " );
          str.append("'"+ mapTypeToString(elem.getType()) +"' , ");
          str.append( "'"+ geom.getDesc() +"' , " );
          str.append( "'"+ elem.getDftTempName() +"' , " );
          str.append( geom.x1Stored + ", " );
          str.append( geom.y1Stored + ", " );
          str.append( geom.x2Stored + ", " );    //In new PAD mdb format, (x2,y2) will be saved
          str.append( geom.y2Stored + ", " );    //Not (x3, y3) for transfer_switch.
          str.append( ((geom.getRotation()+3)%4) + ", " ); //rotate 0: south
          str.append( geom.getLTC() + ","  );      //RTC
          str.append( isSwitched +"," );
          str.append( isOpen +", " );
          str.append( showText[0] +","+ showText[1] +"," );  //show name, show desc
          str.append( showText[2] +","+ showText[7] +"," );  //show data0, show result0
          str.append( showText[3] +","+ showText[8] +"," );  //show data1, show result1
          str.append( showText[4] +","+ showText[9] +"," );  //show data2, show result2
          str.append( showText[5] +","+ showText[10] +"," ); //show data3, show result3
          str.append( showText[6] +","+ showText[11] +"," ); //show data4, show result4
          str.append( elem.getData(AbsElem.DATA_LENGTH) +","+ elem.getFlag(AbsElem.DATA_LENGTH) +"," );
          str.append( elem.getData(AbsElem.DATA_ZONE) +","+ elem.getFlag(AbsElem.DATA_ZONE) +"," );
          str.append( elem.getData(AbsElem.DATA_CUSTOMERS) +","+ elem.getFlag(AbsElem.DATA_CUSTOMERS) +"," );
          str.append( elem.getData(AbsElem.DATA_TEMP_FAULT) +","+ elem.getFlag(AbsElem.DATA_TEMP_FAULT) +"," );
          str.append( elem.getData(AbsElem.DATA_SUST_FAULT) +","+ elem.getFlag(AbsElem.DATA_SUST_FAULT) +"," );
          str.append( elem.getData(AbsElem.DATA_OPEN_CKT) +","+ elem.getFlag(AbsElem.DATA_OPEN_CKT) +"," );
          str.append( elem.getData(AbsElem.DATA_MTTR) +","+ elem.getFlag(AbsElem.DATA_MTTR) +"," );
          str.append( elem.getData(AbsElem.DATA_MTTS) +","+ elem.getFlag(AbsElem.DATA_MTTS) +"," );
          str.append( elem.getData(AbsElem.DATA_OPER_FAIL) +","+ elem.getFlag(AbsElem.DATA_OPER_FAIL) +"," );
          str.append( isNormOpen +","+ elem.getFlag(AbsElem.DATA_NORM_OPEN) +"," );
          str.append( elem.getData(AbsElem.DATA_CAPITAL_COST) +","+ elem.getFlag(AbsElem.DATA_CAPITAL_COST) +"," );
          str.append( elem.getData(AbsElem.DATA_OM_COST) +","+ elem.getFlag(AbsElem.DATA_OM_COST) +"," );
          str.append( elem.getData(AbsElem.DATA_COST_PER_INT_KW) +","+ elem.getFlag(AbsElem.DATA_COST_PER_INT_KW) +"," );
          str.append( elem.getData(AbsElem.DATA_COST_PER_INT_KWH) +","+ elem.getFlag(AbsElem.DATA_COST_PER_INT_KWH) +"," );
          str.append( elem.getData(AbsElem.DATA_CONST_POWER) +","+ elem.getFlag(AbsElem.DATA_CONST_POWER) +"," );
          str.append( elem.getData(AbsElem.DATA_PF_CONST_POWER) +","+ elem.getFlag(AbsElem.DATA_PF_CONST_POWER) +"," );
          str.append( elem.getData(AbsElem.DATA_CONST_CURR) +","+ elem.getFlag(AbsElem.DATA_CONST_CURR) +"," );
          str.append( elem.getData(AbsElem.DATA_PF_CONST_CURR) +","+ elem.getFlag(AbsElem.DATA_PF_CONST_CURR) +"," );
          str.append( elem.getData(AbsElem.DATA_RATED_POWER) +","+ elem.getFlag(AbsElem.DATA_RATED_POWER) +"," );
          str.append( elem.getData(AbsElem.DATA_RATED_CURR) +","+ elem.getFlag(AbsElem.DATA_RATED_CURR) +"," );
          str.append( elem.getData(AbsElem.DATA_SRC_VOLT) +","+ elem.getFlag(AbsElem.DATA_SRC_VOLT) +"," );
          str.append( elem.getData(AbsElem.DATA_RESISTANCE) +","+ elem.getFlag(AbsElem.DATA_RESISTANCE) +"," );
          str.append( elem.getData(AbsElem.DATA_REACTANCE) +","+ elem.getFlag(AbsElem.DATA_REACTANCE) +"," );
          str.append( elem.getData(AbsElem.DATA_SHUNT_COND) +","+ elem.getFlag(AbsElem.DATA_SHUNT_COND) +"," );
          str.append( elem.getData(AbsElem.DATA_SHUNT_SUCEP) +","+ elem.getFlag(AbsElem.DATA_SHUNT_SUCEP) +"," );
          str.append( elem.getData(AbsElem.DATA_TXFM_RATIO) +","+ elem.getFlag(AbsElem.DATA_TXFM_RATIO) +"," );
          str.append( elem.getData(AbsElem.DATA_TAP_RATIO) +","+ elem.getFlag(AbsElem.DATA_TAP_RATIO) +"," );
          str.append( elem.getData(AbsElem.DATA_NUM_BOOST) +","+ elem.getFlag(AbsElem.DATA_NUM_BOOST) +"," );
          str.append( elem.getData(AbsElem.DATA_NUM_BUCK) +","+ elem.getFlag(AbsElem.DATA_NUM_BUCK) +"," );
          str.append( elem.getData(AbsElem.DATA_BOOST_TRIG) +","+ elem.getFlag(AbsElem.DATA_BOOST_TRIG) +"," );
          str.append( elem.getData(AbsElem.DATA_BUCK_TRIG) +","+ elem.getFlag(AbsElem.DATA_BUCK_TRIG) +"," );
          str.append( elem.getData(AbsElem.DATA_VOLT_SAG_LEVEL) +","+ elem.getFlag(AbsElem.DATA_VOLT_SAG_LEVEL) +"," );
          str.append( elem.getData(AbsElem.DATA_COST_PER_SAG) +","+ elem.getFlag(AbsElem.DATA_COST_PER_SAG) +"," );
          str.append( isLeading +","+ elem.getFlag(AbsElem.DATA_IS_LEADING) +"," );
          str.append( elem.getData(AbsElem.DATA_UPGRADE_COST) +","+ elem.getFlag(AbsElem.DATA_UPGRADE_COST) +"," );
          str.append( elem.getData(AbsElem.DATA_PHASE) +","+ elem.getFlag(AbsElem.DATA_PHASE) +"," );
          str.append( elem.getData(AbsElem.DATA_FONT_SIZE) +","+ elem.getFlag(AbsElem.DATA_FONT_SIZE) +"," );
          str.append( isInStudyArea +","+ elem.getFlag(AbsElem.DATA_STUDY_AREA) +"," );
          str.append( reclosingRelay +","+ elem.getFlag(AbsElem.DATA_RECLOSING) +"," );
          str.append( instantBlocked +","+ elem.getFlag(AbsElem.DATA_INST_BLOCKED) +"," );
          str.append( isInOptLib +","+ elem.getFlag(AbsElem.DATA_IN_OPT_LIB) +"," );
          str.append( isOptimizable +","+ elem.getFlag(AbsElem.DATA_OPTIMIZABLE) +"," );
          str.append( elem.getData(AbsElem.DATA_LAYER) +","+ elem.getFlag(AbsElem.DATA_LAYER) +"," );
          str.append( elem.getData(AbsElem.DATA_MAINT_RATE) +","+ elem.getFlag(AbsElem.DATA_MAINT_RATE) +"," );
          str.append( elem.getData(AbsElem.DATA_MAINT_DUR) +","+ elem.getFlag(AbsElem.DATA_MAINT_DUR) +"," );
          str.append( elem.getData(AbsElem.DATA_ZONE_BUFFER0) +","+ elem.getFlag(AbsElem.DATA_ZONE_BUFFER0) +"," );
          str.append( elem.getData(AbsElem.DATA_ZONE_BUFFER1) +","+ elem.getFlag(AbsElem.DATA_ZONE_BUFFER1) +"," );
          str.append( elem.getData(AbsElem.DATA_ZONE_BUFFER2) +","+ elem.getFlag(AbsElem.DATA_ZONE_BUFFER2) +"," );
          str.append( elem.getData(AbsElem.DATA_ZONE_BUFFER3) +","+ elem.getFlag(AbsElem.DATA_ZONE_BUFFER3) +"," );
          str.append( elem.getData(AbsElem.DATA_ZONE_BUFFER4) +","+ elem.getFlag(AbsElem.DATA_ZONE_BUFFER4) +"," );
          str.append( elem.getData(AbsElem.DATA_LAYER) +","+ elem.getFlag(AbsElem.DATA_YEAR) );

        return str.toString();
    }

  private void findShowText( AbsElem elem, int[] showText){
      AbsElemGeom geom = elem.getElemGeomObj();
      showText[0] = (geom.getShowName() ? 1 : 0);
      showText[1] = (geom.getShowDesc() ? 1 : 0);
      if (geom.getShowIndex(AbsElemGeom.SHOW_DATA)) {
          // A maximum of five indices is saved.
          int count = Math.min(5,
              geom.getShowIndexCount(AbsElemGeom.SHOW_DATA));
          for (int i = 0; i < count; i++) {
              int index = geom.getShowIndex(AbsElemGeom.SHOW_DATA, i);
              // Search for the index in the "old" mdb format
              for (int j = 0; j < mapOldDataIndexToElemDataIndex.length; j++) {
                  if (index == mapOldDataIndexToElemDataIndex[j]) {
                      // "old index fount, save it.
                      showText[i + 2] = j;
                      break;
                  }
              }
          }
      }
      if (geom.getShowIndex(AbsElemGeom.SHOW_RESULT)) {
          int count = Math.min(5,
              geom.getShowIndexCount(AbsElemGeom.SHOW_RESULT));
          for (int i = 0; i < count; i++) {
              int index = geom.getShowIndex(AbsElemGeom.SHOW_RESULT, i);
              for (int j = 0; j < mapOldResultIndexToElemResultIndex.length; j++) {
                  if (index == mapOldResultIndexToElemResultIndex[j]) {
                      showText[i + 7] = j;
                      break;
                  }
              }
          }
      }
  }
}// end of Class DBAccessor

